package worklog.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import worklog.Holidays;
import worklog.dto.DayDTO;
import worklog.dto.EntryDTO;



public class EntryDB {
	private MyDB db;
	private int rowsPerDay = 8;

    public EntryDB(){ 
    	try { db = new MyDB(); }
    	catch(Exception e){ db = null; }
    }
	protected void finalize(){ db.close(); }

	public void close(){ db.close(); }
	
	public Boolean isReady(){ return (this.db!=null); }

	
	
	/**
	 * Retrieves 1 entry by its ID
	 * @throws SQLException 
	 */
	public EntryDTO getEntry(int id) throws SQLException {
		EntryDTO entry = null;

		PreparedStatement statement = db.connection
				.prepareStatement("SELECT entry.*, project.name AS projectName " +
					"FROM entry LEFT JOIN project ON entry.projectID = project.id WHERE id=?");

		statement.setInt(1, id);
		ResultSet res = statement.executeQuery();

		if (res != null && res.next()) {
			entry = new EntryDTO();
			entry.set(res);
		}
		statement.close();

		return entry;
	}

	/**
	 * Retrieves x entries by the day date
	 * @throws SQLException 
	 */
	public DayDTO getDay(int userId, String date) throws SQLException{
		DayDTO day = new DayDTO();
		
		PreparedStatement statement = db.connection
				.prepareStatement("SELECT entry.*, project.name AS projectName "+
						"FROM entry LEFT JOIN project ON entry.projectID = project.id "+
						"WHERE entry.userId=? AND day=? ORDER BY type");
		
		statement.setInt(1, userId);
		statement.setString(2, date);
		ResultSet res = statement.executeQuery();

		if (res != null){
			EntryDTO[] entries = new EntryDTO[rowsPerDay];
			EntryDTO entry;
			while (res.next()){ 
				entry = new EntryDTO();
				entry.set(res);
				entries[entry.getType()-1] = entry;
			}
			Holidays holis = new Holidays(date);
			String holidayName = holis.isHoliday(date);
			Boolean isHoliday = (holidayName != null);
			
			day.setDate(date);
			day.setRows(entries);
			day.setIsHoliday(isHoliday);
			day.setHolidayName(holidayName);
		}
		
		statement.close();
		
		return day;
	}

	
	
	
	/**
	 * Retrieves x entries by the day date
	 * @throws SQLException 
	 */
	public DayDTO getProjectDay(String date, int projectId, int[] userIds) throws SQLException{
		DayDTO day = new DayDTO();
		
		String users = ""; 		//AND (userID = 11 OR userID = 6)
		if (userIds != null && userIds.length > 0){
			for (int id : userIds) users += " OR entry.userId = " + id;
			users = " AND (" + users.substring(4) + ")";
		}
		
		String project = "";
		if (projectId > 0){		// AND projectId = ?
			project = " AND entry.projectId = " + projectId;
		}
		
		PreparedStatement statement = db.connection.prepareStatement(
			"SELECT entry.id, entry.day, entry.type, entry.projectID, project.name AS projectName, entry.userID, user.login AS userName, entry.description "+
			"FROM entry LEFT JOIN project ON entry.projectID = project.id LEFT JOIN user ON entry.userID = user.id "+
			"WHERE (entry.projectId<>0 OR entry.description<>'') AND day=? " + project + users + " ORDER BY entry.type, projectName"
		);
		statement.setString(1, date);
		ResultSet res = statement.executeQuery();

		if (res != null){
			ArrayList<EntryDTO> entries = new ArrayList<EntryDTO>();
			EntryDTO entry;
			while (res.next()){ 
				entry = new EntryDTO();
				entry.set(res);
				entries.add(entry);
			}
			Holidays holis = new Holidays(date);
			String holidayName = holis.isHoliday(date);
			Boolean isHoliday = (holidayName != null);
			
			day.setDate(date);
			day.setRows(entries.toArray(new EntryDTO[entries.size()]));
			day.setIsHoliday(isHoliday);
			day.setHolidayName(holidayName);
		}
		
		statement.close();
		
		return day;
	}
	
	
	
	/**
	 * Checks whether an entry exists
	 */
	public int getEntryId(int userId, String date, int type){
		int id = 0;
		PreparedStatement statement;
		try {
			statement = db.connection.prepareStatement("SELECT id FROM entry WHERE userId = ? AND day = ? AND type = ?");
			statement.setInt(1, userId);
			statement.setString(2, date);
			statement.setInt(3, type);
			ResultSet res = statement.executeQuery();
			if (res != null && res.next()) id = res.getInt("id");
			statement.close();
		} 
		catch (SQLException e){}
		return id;
	}

	
	
	/**
	 * Saves x entries by the day date
	 */
	public int saveDay(EntryDTO[] entries, int userId){
		PreparedStatement statement;
		int res, succ = 0;
		try {
			for (int i = 0; i < rowsPerDay; i++){
				int id = getEntryId(userId, entries[i].getDay(), entries[i].getType());
				if (id != 0){		// update		
					statement = db.connection.prepareStatement("UPDATE entry SET projectID = ?, description = ? WHERE id = ?");
					statement.setInt(1, entries[i].getProjectId());
					statement.setString(2, entries[i].getDescription());
					statement.setInt(3, id);
				}
				else {				// add new
					statement = db.connection.prepareStatement("INSERT INTO entry(userID, day, type, projectID, description) VALUES (?, ?, ?, ?, ?)");
					statement.setInt(1, userId);
					statement.setString(2, entries[i].getDay());
					statement.setInt(3, entries[i].getType());
					statement.setInt(4, entries[i].getProjectId());
					statement.setString(5, entries[i].getDescription());
				}
				res = statement.executeUpdate();
				if (res > 0) succ++;
			} 
		}
		catch (SQLException e) { 
			e.printStackTrace(); 
		}		
		
		return succ;
	}	
}
